package com.wswy.wyjk.db

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.my.httpapi.api.baseUtils.LogUtils
import com.wswy.wyjk.BuildConfig
import com.wswy.wyjk.model.Chapter
import com.wswy.wyjk.model.PracticeData
import com.wswy.wyjk.model.PracticeType
import com.wswy.wyjk.model.RecordData
import com.wswy.wyjk.model.dto.*
import com.wswy.wyjk.model.vo.ExamResult

import com.wswy.wyjk.utils.rawQuery

class JKDbHelper(context: Context) : SQLiteOpenHelper(context, DB_NAME, null, DB_VERSION),
    DBHelper {
    private companion object {

        private const val SHOW_SQL = true

        private const val DB_NAME = "wyjk_new_3.db"
        private const val DB_VERSION = 3

        private const val TABLE_QUESTION = "question"
        private const val TABLE_CHAPTER = "chapter"
        private const val TABLE_EXAM_REGULAR = "exam_regular"
        private const val TABLE_ERRO = "erro"
        // private const val TABLE_RECORD = "tb_record"  //练习记录
        //  private const val TABLE_EXAM_RECORD = "tb_exam_record"  //临时考试表

        private const val SQL_COMMON_COND =
            " where car_type=? and course=? and  area_code=0 group by chapter_id"

        private const val SQL_COMMON_COND2 =
            " where car_type=?  and  area_code=0  group by chapter_id"

        private const val SQL_IN_CHAPTER =
            " (select chapter_id  from  ${TABLE_EXAM_REGULAR}  ${SQL_COMMON_COND})"

        private const val SQL_IN_CHAPTER2 =
            " (select chapter_id  from  ${TABLE_EXAM_REGULAR}  ${SQL_COMMON_COND2})"

        private const val SQL_WHERE_P = " where chapter_id in $SQL_IN_CHAPTER"

        private const val SQL_WHERE_P2 = " where chapter_id in $SQL_IN_CHAPTER2"

        private const val SQL_EERO_P = " where chapter_id in $SQL_IN_CHAPTER"

        //章节列表条件
        private const val SQL_CHAPTERS_WHERE = " where mid in $SQL_IN_CHAPTER"

        private const val SQL_CHAPTERS_WHERE2 = " where mid in $SQL_IN_CHAPTER2"


        // 统计所有得题目
        private const val SQL_COUNT_P = "select count(id) from ${TABLE_QUESTION} $SQL_WHERE_P"

        // 统计已完成得题目
        private const val SQL_COUNT_P_F =
            "select count(id) from ${TABLE_QUESTION}  $SQL_WHERE_P   and  (is_do=1  or   is_do=2) "

        // 统计错误得题目
        private const val SQL_COUNT_P_ERR =
            "select count(id) from ${TABLE_QUESTION}  $SQL_WHERE_P   and  is_do=2 "

        // 统计正确得题目
        private const val SQL_COUNT_P_RIGHT =
            "select count(id) from ${TABLE_QUESTION}  $SQL_WHERE_P   and  is_do=1 "

        private const val SQL_COUNT_P2 = "select count(id) from ${TABLE_QUESTION} $SQL_WHERE_P2"

        // 统计所有已做题目
//        private const val SQL_COUNT_RECORD =
//            "select count(question_id) from ${TABLE_RECORD} $SQL_WHERE_P and ##!=0"
    }

    override fun onCreate(db: SQLiteDatabase?) {
        db?.let {
            initTable(it)
        }
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {


    }

    private fun initTable(db: SQLiteDatabase) {
        //新建表和索引
        //生成答题记录
//        db.execSQL(
//            "CREATE TABLE IF NOT EXISTS ${TABLE_RECORD}  (question_id INTEGER PRIMARY KEY,chapter_id INTEGER NOT NULL," +
//                    "random_answer INTEGER DEFAULT 0 ,order_answer DEFAULT 0,chapter_answer INTEGER DEFAULT 0,difficult_answer INTEGER DEFAULT 0,undone_answer INTEGER DEFAULT 0);"
//            , BuildConfig.DEBUG
//        )

//        db.execSQL(
//            "CREATE UNIQUE INDEX IF NOT EXISTS IDX_tb_record_question_id ON tb_record " +
//                    " (\"question_id\" ASC);"
//            , BuildConfig.DEBUG
//        )
//
//        db.execSQL(
//            "CREATE INDEX IF NOT EXISTS IDX_tb_record_chapter_id ON  tb_record " +
//                    " (\"chapter_id\" ASC);"
//            , BuildConfig.DEBUG
//        )

        //生成临时考试表
//        db.execSQL(
//            "CREATE TABLE IF NOT EXISTS ${TABLE_EXAM_RECORD} (_id INTEGER PRIMARY KEY AUTOINCREMENT, car_type TEXT , course TEXT, create_time INTEGER DEFAULT 0, exam_time INTEGER DEFAULT 0, score INTEGER ,approved INTEGER);"
//            , BuildConfig.DEBUG
//        )

        //复制数据
//        if (countCursor(
//                db.rawQuery(
//                    "select count(question_id) from ${TABLE_RECORD}",
//                    null,
//                    BuildConfig.DEBUG
//                )
//            ) == 0
//        ) {
//            db.execSQL(
//                "insert into ${TABLE_RECORD}(question_id,chapter_id)  select  id,chapter_id from ${TABLE_QUESTION}",
//                BuildConfig.DEBUG
//            )
//        }
    }

    /**
     * 所有题库
     */
    override fun findAllPractice(practiceType: PracticeType): List<PracticeData>? {
        val retList = ArrayList<PracticeData>()

        var sql: String? = null
        var args: Array<String>? = null

        if (practiceType.type == PracticeType.TYPE_CHAPTER) {
            //章节
            if (practiceType.subjectType.title.equals("资格证")) {
                sql = "select * from ${TABLE_QUESTION} where chapter_id=? "
                args = arrayOf(practiceType.chapterId.toString())
            } else {
                sql = "select * from ${TABLE_QUESTION} where chapter_id=? and  course=?"
                args = arrayOf(practiceType.chapterId.toString(), practiceType.subjectType.typeName)
            }

        } else if (practiceType.type == PracticeType.TYPE_DIFFICULTY) {
            //难题
            if (practiceType.subjectType.title.equals("资格证")) {
                sql = "select * from ${TABLE_QUESTION}  ${SQL_WHERE_P2}  and  diff_degree =5"
                args = arrayOf(
                    practiceType.licenseType.typeName
                )
            } else {
                sql = "select * from ${TABLE_QUESTION}  ${SQL_WHERE_P}  and  diff_degree =5"
                args = arrayOf(
                    practiceType.licenseType.typeName,
                    practiceType.subjectType.typeName
                )
            }

        } else if (practiceType.type == PracticeType.TYPE_ERROR) {
            //错题
//            sql =
//                "select * from ${TABLE_QUESTION} where id in " + " (select question_id  from  ${TABLE_ERRO}   group by question_id )"

            sql = "select * from ${TABLE_QUESTION} where id in " + practiceType.sql

        } else if (practiceType.type == PracticeType.TYPE_EXAM) {
            if (practiceType.subjectType.title.equals("资格证")) {
                sql =
                    "select * from ${TABLE_QUESTION} ${SQL_WHERE_P2}  Order BY RANDOM()  limit 100 "
                args = arrayOf(
                    practiceType.licenseType.typeName
                )
            } else {
                if (practiceType.subjectType.title.equals("科目一")) {
                    sql =
                        "select * from ${TABLE_QUESTION} ${SQL_WHERE_P}  Order BY RANDOM()  limit 100 "
                } else {
                    sql =
                        "select * from ${TABLE_QUESTION} ${SQL_WHERE_P}  Order BY RANDOM()  limit 50"
                }
                args = arrayOf(
                    practiceType.licenseType.typeName,
                    practiceType.subjectType.typeName
                )
            }
        } else if (practiceType.type == PracticeType.TYPE_RANDOM) {
            if (practiceType.subjectType.title.equals("资格证")) {
                sql = "select * from ${TABLE_QUESTION} ${SQL_WHERE_P2}  Order BY RANDOM() "
                args = arrayOf(practiceType.licenseType.typeName)
            } else {
                sql = "select * from ${TABLE_QUESTION} ${SQL_WHERE_P}  Order BY RANDOM() "
                args = arrayOf(
                    practiceType.licenseType.typeName,
                    practiceType.subjectType.typeName
                )
            }

        } else if (practiceType.type == PracticeType.TYPE_UNDONE) {
            if (practiceType.subjectType.title.equals("资格证")) {
                sql = "select  * from ${TABLE_QUESTION} ${SQL_WHERE_P2} and is_do is null"
                // sql = "select * from ${TABLE_QUESTION}  where car_type like ? and course=? "
                args = arrayOf(practiceType.licenseType.typeName)
            } else {
                sql = "select  * from ${TABLE_QUESTION} ${SQL_WHERE_P} and is_do is null"
                // sql = "select * from ${TABLE_QUESTION}  where car_type like ? and course=? "
                args = arrayOf(
                    practiceType.licenseType.typeName,
                    practiceType.subjectType.typeName
                )
            }

        } else {
            if (practiceType.subjectType.title.equals("资格证")) {
                sql = "select  * from ${TABLE_QUESTION} ${SQL_WHERE_P2}"
                // sql = "select * from ${TABLE_QUESTION}  where car_type like ? and course=? "
                args = arrayOf(practiceType.licenseType.typeName)
            } else {
                sql = "select  * from ${TABLE_QUESTION} ${SQL_WHERE_P}"
                // sql = "select * from ${TABLE_QUESTION}  where car_type like ? and course=? "
                args = arrayOf(
                    practiceType.licenseType.typeName,
                    practiceType.subjectType.typeName
                )
            }
        }
        readableDatabase.rawQuery(sql, args, BuildConfig.DEBUG)?.use {
            while (it.moveToNext()) {
                retList.add(PracticeData(mapPracticeDto(it)))
            }
        }

        return retList
    }

    /**
     * 所有记录
     */
    override fun findAllRecord(practiceType: PracticeType): List<RecordData>? {
        val retList = ArrayList<RecordData>()

//        if (practiceType.type == PracticeType.TYPE_CHAPTER) {
//
////            readableDatabase.rawQuery(
////                "select * from ${TABLE_RECORD} where chapter_id=?",
////                arrayOf(practiceType.chapterId.toString()),
////                BuildConfig.DEBUG
////            )
//        } else {
//            readableDatabase.rawQuery(
//                "select * from ${TABLE_RECORD} ${SQL_WHERE_P}",
//                arrayOf(
//                    practiceType.licenseType.typeName,
//                    practiceType.subjectType.typeName
//                ),
//                BuildConfig.DEBUG
//            )
//        }?.use {
//            while (it.moveToNext()) {
//                retList.add(RecordData(mapRecordDto(it), practiceType))
//            }
//        }
        return retList
    }

    /**
     * 所有章节列表
     */
    override fun findAllChapter(practiceType: PracticeType): List<Chapter>? {
        var sql: String? = null
        var args: Array<String>? = null
        val retList = ArrayList<Chapter>()
        if (practiceType.subjectType.title.equals("资格证")) {
            sql = "select * from ${TABLE_CHAPTER} ${SQL_CHAPTERS_WHERE2} "
            args = arrayOf(practiceType.licenseType.typeName)
        } else {
            sql = "select * from ${TABLE_CHAPTER} ${SQL_CHAPTERS_WHERE} and course=?"
            args = arrayOf(
                practiceType.licenseType.typeName,
                practiceType.subjectType.typeName,
                practiceType.subjectType.typeName
            )
        }
        readableDatabase.rawQuery(sql, args, BuildConfig.DEBUG)?.use {
            while (it.moveToNext()) {

                mapChapterDto(it).run {
                    retList.add(Chapter(id, title, count))
                }
            }
        }
        return retList
    }

    /**
     * 获取考试规则
     */
    override fun getExamRegulars(practiceType: PracticeType): List<ExamRegular>? {
        val retList = ArrayList<ExamRegular>()

        readableDatabase.rawQuery(
            "select * from ${TABLE_EXAM_REGULAR} ${SQL_COMMON_COND}",
            getCommonArgs(practiceType), BuildConfig.DEBUG
        )?.use {
            while (it.moveToNext()) {
                retList.add(mapExamRegularDto(it))
            }
        }

        return retList
    }


    override fun findAllDifficult(practiceType: PracticeType): List<Pair<PracticeData, RecordData>>? {
        val retList = ArrayList<Pair<PracticeData, RecordData>>()
        readableDatabase.rawQuery(
            "select  *  from  ${TABLE_QUESTION} as q  join  tb_record as r on q.id = r.question_id where q.chapter_id  in  ${SQL_IN_CHAPTER} order by q.difficulty desc,q.wrong_rate desc limit 0,100",
            getCommonArgs(practiceType), BuildConfig.DEBUG
        )?.use {
            while (it.moveToNext()) {

                retList.add(
                    Pair(
                        PracticeData(mapPracticeDto(it)),
                        RecordData(mapRecordDto(it), practiceType)
                    )
                )
            }
        }

        return retList
    }

    /**
     * 题目数量
     */
    override fun countPractice(practiceType: PracticeType): Int {
        if (practiceType.subjectType.title.equals("资格证")) {
            return countCursor(
                readableDatabase.rawQuery(
                    SQL_COUNT_P2,
                    getCommon2Args(practiceType), BuildConfig.DEBUG
                )
            )
        }
        return countCursor(
            readableDatabase.rawQuery(
                SQL_COUNT_P,
                getCommonArgs(practiceType), BuildConfig.DEBUG
            )
        )
    }

    override fun upDateData(id: Int?, idDo: Int?, doA: Int?) {
        val values = ContentValues(2)

        //保存结果
        values.put("is_do", idDo.toString())
        values.put("do_a", doA.toString())

        readableDatabase.update(
            TABLE_QUESTION,
            values,
            "id=?",
            arrayOf("$id")
        )
        LogUtils.e("修改成功")
    }

    /**
     * 已完成数量
     */
    override fun countComplete(practiceType: PracticeType): Int {
        return countCursor(
            readableDatabase.rawQuery(
                SQL_COUNT_P_F,
                getCommonArgs(practiceType), BuildConfig.DEBUG
            )
        )
        return 0;
    }

    /**
     * 错误的数量
     */
    override fun countErr(practiceType: PracticeType): Int {
        return countCursor(
            readableDatabase.rawQuery(
                SQL_COUNT_P_ERR,
                getCommonArgs(practiceType), BuildConfig.DEBUG
            )
        )
        return 0;
    }

    /**
     * 正确的数量
     */
    override fun countRight(practiceType: PracticeType): Int {
        return countCursor(
            readableDatabase.rawQuery(
                SQL_COUNT_P_RIGHT,
                getCommonArgs(practiceType), BuildConfig.DEBUG
            )
        )
        return 0;
    }

    override fun saveExamRecord(examResult: ExamResult) {

        val values = ContentValues(6)

        //保存结果
        values.put("car_type", examResult.practiceType.licenseType.typeName)
        values.put("course", examResult.practiceType.subjectType.typeName)
        values.put("create_time", System.currentTimeMillis())
        values.put("exam_time", examResult.time)
        values.put("score", examResult.score)
        values.put("approved", if (examResult.isApproved) 1 else 0)

        // writableDatabase.insert(TABLE_EXAM_RECORD, null, values)
    }

    override fun AddErrData(id: Int?, car_type: String?, course: Int?) {
        val values = ContentValues(3)
        //保存结果
        values.put("question_id", id)
        values.put("car_type", car_type)
        values.put("course", course)

        writableDatabase.insert(TABLE_ERRO, null, values)
        LogUtils.e("插入成功")
    }

    override fun DeteErrData(id: Int?) {
        writableDatabase.delete(TABLE_ERRO, "question_id=?", arrayOf("$id"))
        LogUtils.e("删除成功")
    }

    override fun findAllExamRecord(practiceType: PracticeType): List<ExamRecord>? {
        val retList = ArrayList<ExamRecord>()
//        readableDatabase.rawQuery(
//            "select * from ${TABLE_EXAM_RECORD} where car_type=? and course=? order by create_time desc",
//            arrayOf(practiceType.licenseType.typeName, practiceType.subjectType.typeName)
//        )?.use {
//
//            while (it.moveToNext()) {
//
//                retList.add(mapExamRecord(it))
//            }
//
//        }
        return retList
    }

    /**
     * 保存记录
     */
    override fun saveRecord(recordDto: RecordDto?): Boolean {
        if (recordDto != null && recordDto.questionId > 0) {

            val values = ContentValues(5)

            //保存结果
            values.put("order_answer", recordDto.orderAnswer)
            values.put("random_answer", recordDto.randomAnswer)
            values.put("chapter_answer", recordDto.chapterAnswer)
            values.put("difficult_answer", recordDto.difficultAnswer)
            values.put("undone_answer", recordDto.undoneAnswer)

//            return writableDatabase.updateWithOnConflict(
//                TABLE_RECORD,
//                values,
//                "question_id=?",
//                arrayOf("${recordDto.questionId}"),
//                SQLiteDatabase.CONFLICT_REPLACE
//            ) == 1
        }

        return false
    }


    private fun getCommonArgs(practiceType: PracticeType): Array<String> = arrayOf(
        practiceType.licenseType.typeName,
        practiceType.subjectType.typeName
    )

    private fun getCommon2Args(practiceType: PracticeType): Array<String> = arrayOf(
        practiceType.licenseType.typeName
    )

    private fun countCursor(cursor: Cursor?): Int {
        return cursor?.let {
            if (it.moveToFirst()) {
                it.getInt(0)
            } else {
                0
            }
        } ?: 0
    }

    private fun mapPracticeDto(cursor: Cursor): PracticeDto {
        return PracticeDto().apply {
            questionId = cursor.getInt(cursor.getColumnIndex("id"))
            chapterId = cursor.getInt(cursor.getColumnIndex("chapter_id"))
            //  label = cursor.getString(cursor.getColumnIndex("label"))
            question = cursor.getString(cursor.getColumnIndex("question"))
            mediaUrl = cursor.getString(cursor.getColumnIndex("media_url"))
            answer = cursor.getInt(cursor.getColumnIndex("correct_answer"))
            optionA = cursor.getString(cursor.getColumnIndex("an1"))
            optionB = cursor.getString(cursor.getColumnIndex("an2"))
            optionC = cursor.getString(cursor.getColumnIndex("an3"))
            optionD = cursor.getString(cursor.getColumnIndex("an4"))
            optionType = cursor.getInt(cursor.getColumnIndex("type"))
            explain = cursor.getString(cursor.getColumnIndex("explain"))
            //  conciseExplain = cursor.getString(cursor.getColumnIndex("concise_explain"))
            keywords = cursor.getString(cursor.getColumnIndex("keyword"))
            difficulty = cursor.getInt(cursor.getColumnIndex("diff_degree"))
            isDo = cursor.getString(cursor.getColumnIndex("is_do"))
            doA = cursor.getString(cursor.getColumnIndex("do_a"))
            //  wrongRate = cursor.getDouble(cursor.getColumnIndex("wrong_rate"))

        }
    }

    private fun mapRecordDto(cursor: Cursor) = RecordDto().apply {

        questionId = cursor.getInt(cursor.getColumnIndex("question_id"))
        chapterId = cursor.getInt(cursor.getColumnIndex("chapter_id"))

        randomAnswer = cursor.getInt(cursor.getColumnIndex("random_answer"))
        orderAnswer = cursor.getInt(cursor.getColumnIndex("order_answer"))
        chapterAnswer = cursor.getInt(cursor.getColumnIndex("chapter_answer"))
        difficultAnswer = cursor.getInt(cursor.getColumnIndex("difficult_answer"))
        undoneAnswer = cursor.getInt(cursor.getColumnIndex("undone_answer"))
    }

    private fun mapChapterDto(cursor: Cursor) = ChapterDto().apply {
        id = cursor.getLong(cursor.getColumnIndex("mid"))
        title = cursor.getString(cursor.getColumnIndex("title"))
        count = cursor.getInt(cursor.getColumnIndex("counts"))
        // chapter = cursor.getInt(cursor.getColumnIndex("chapter"))
    }


    private fun mapExamRegularDto(cursor: Cursor) = ExamRegular().apply {

        carType = cursor.getString(cursor.getColumnIndex("car_type"))
        course = cursor.getString(cursor.getColumnIndex("course"))
        chapterId = cursor.getInt(cursor.getColumnIndex("chapter_id"))
        count = cursor.getInt(cursor.getColumnIndex("count"))

        count1 = cursor.getInt(cursor.getColumnIndex("count1"))
        count2 = cursor.getInt(cursor.getColumnIndex("count2"))
        count3 = cursor.getInt(cursor.getColumnIndex("count3"))

    }

    private fun mapExamRecord(cursor: Cursor) = ExamRecord().apply {
        carType = cursor.getString(cursor.getColumnIndex("car_type"))
        course = cursor.getString(cursor.getColumnIndex("course"))
        createTime = cursor.getLong(cursor.getColumnIndex("create_time"))
        examTime = cursor.getInt(cursor.getColumnIndex("exam_time"))
        score = cursor.getInt(cursor.getColumnIndex("score"))
        isApproved = cursor.getInt(cursor.getColumnIndex("approved")) == 1
    }

}